from sqlalchemy import Column, String, create_engine, Integer, Boolean, Date, ForeignKey, Float
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from local_settings import DB_URI
from werkzeug.security import generate_password_hash, check_password_hash

engine = create_engine(DB_URI)
dbbase = declarative_base()

class User(dbbase):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    name = Column(String(60))
    password = Column(String(170))
    role = Column(Integer)
    deleted = Column(Boolean, default=False)

class Goods(dbbase):
    __tablename__ = 'goods'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    gcode = Column(String(128))
    gname = Column(String(20))
    brand = Column(String(20))
    model = Column(String(20))
    manufacturer = Column(String(30))
    measure = Column(String(10))
    purchases = relationship('Purchase', )

class Supplier(dbbase):
    __tablename__ = 'supplier'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    scode = Column(String(128))
    name = Column(String(30))
    address = Column(String(30))
    contact = Column(String(10))
    phone = Column(String(15))
    purchases = relationship('Purchase')

class Purchase(dbbase):
    __tablename__ = 'purchase'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    pcode = Column(String(128))
    pdate = Column(Date)
    pgoods = Column(Integer, ForeignKey('goods.id'))
    quantity = Column(Integer)
    univalence = Column(Float)
    supplier = Column(Integer, ForeignKey('supplier.id'))
    deleted = Column(Boolean,default=False)

class Customer(dbbase):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    ccode = Column(String(128))
    name = Column(String(20))
    contact = Column(String(10))
    phone = Column(String(15))
    deleted = Column(Boolean, default=False)

class Sell(dbbase):
    __tablename__ = 'sell'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    scode = Column(String(128))
    sdate = Column(Date)
    sgoods = Column(Integer, ForeignKey('goods.id'))
    quantity = Column(Integer)
    univalence = Column(Float)
    customer = Column(Integer, ForeignKey('customer.id'))
    deleted = Column(Boolean, default=False)

class Stock(dbbase):
    __tablename__ = 'stock'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    goods = Column(Integer, ForeignKey('goods.id'))
    quantity = Column(Integer)
    deleted = Column(Boolean, default=False)



class Dbconn():
    def __init__(self):
        self.engine = create_engine(DB_URI)
        self.dbs = sessionmaker(bind=self.engine)
        self.cursor = self.dbs()

    def close(self):
        self.cursor.close()

    def commit(self):
        self.cursor.commit()

    def verify_password(self, uname='', password=''):
        passwd = generate_password_hash(password)
        result = self.cursor.query(User).filter(User.name==uname, User.deleted==False).first()
        if result:
            if check_password_hash(result.password, password):
                return {'id':result.id, 'name':result.name}
            else:
                return False
        else:
            return False
    def get_user(self, id):
        result = self.cursor.query(User).filter(User.id==id).first()
        if result:
            return result
        else:
            return None



if __name__ == '__main__' :
    # dbbase.metadata.drop_all(bind=engine)
    # dbbase.metadata.create_all(bind=engine)

    # dbsession = sessionmaker(bind=engine)
    # session = dbsession()
    # uname = 'user1'
    # password = 'qwer142536'
    # passwd = generate_password_hash(password)
    # role为1是管理员，2为普通用户
    # user = User(name=uname, password=passwd, role=2)
    # session.add(user)
    # ls = session.query(User).filter().all()
    # ls = session.query(User).filter(User.id==1).one()
    # for i in ls:
        # print(i.id, i.name, i.password)
    # session.commit()
    # session.close()
    # db = Dbconn()
    # r = db.verify_password('lzlx', 'qwer142536')
    # print(r)
    pass





